package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDALocation;
import ro.gateway.aida.obj.Country;
import ro.gateway.aida.obj.County;
import ro.gateway.aida.obj.Region;
import ro.gateway.aida.obj.Town;
import ro.gateway.aida.obj.Village;
import ro.gateway.aida.servlet.Constants;


/**
 * <p>Title: Romanian AIDA</p>
 * <p>Description: :D application</p>
 * <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 *
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: VillageDB.java,v 1.2 2005/01/26 10:35:58 mihaipostelnicu Exp $
 */
public class VillageDB extends DBPersistenceManager {
	private VillageDB(PersistenceToken token) {
		super(token);
	}

	public static VillageDB getManager(PersistenceToken token) {
		return new VillageDB(token);
	}

	public void insertVillage(Village village)
		 throws SQLException {
		if (village.getSiruta_id() == -1) {
			village.setSiruta_id((int) db_token.getIdgenerators()
				 .nextVal(Constants.IDGEN_VILLAGES));
		}

		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(INSERT_VILLAGE);
			ps.setInt(1, village.getSiruta_id());
			ps.setInt(2, village.getSirsup_id());
			ps.setString(3, village.getName());
			ps.setDouble(4, village.getGeo_data().getLatitude());
			ps.setDouble(5, village.getGeo_data().getLongitude());
			ps.setString(6, village.getGeo_data().getGeo_code());
			ps.close();
			ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			con.close();
		}
	}

	public void updateVillage(Village village)
		 throws SQLException {

		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(UPDATE_VILLAGE);
			ps.setString(1, village.getName());
			ps.setDouble(2, village.getGeo_data().getLatitude());
			ps.setDouble(3, village.getGeo_data().getLongitude());
			ps.setString(4, village.getGeo_data().getGeo_code());
			ps.setInt(5, village.getSiruta_id());
			ps.close();
			ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			con.close();
		}
	}

	public Village getById(int siruta_id)
		 throws SQLException {
		Connection con = getConnection();
		Village result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_ID);
			ps.setInt(1, siruta_id);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				result = new Village();
				result.setSiruta_id(siruta_id);
				result.setName(rs.getString(1));
				result.setSirsup_id(rs.getInt(2));
				result.getGeo_data().setLatitude(rs.getDouble(3));
				result.getGeo_data().setLongitude(rs.getDouble(4));
				result.getGeo_data().setGeo_code(rs.getString(5));
			}

			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public void deleteVillage(int siruta_id)
		 throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(DELETE_VILLAGE);
			ps.setInt(1, siruta_id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
	}

	public Village[] getAllForTown(int oras_id) throws SQLException {
		Connection con = getConnection();
		ArrayList items = new ArrayList();
		Village[] result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_ALL_FOR_ORAS);
			ps.setInt(1, oras_id);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Village item = new Village();

				item.setSiruta_id(rs.getInt(1));
				item.setName(rs.getString(2));
				item.setSirsup_id(oras_id);

				items.add(item);
			}

			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new Village[items.size()];
			items.toArray(result);
		}

		return result;
	}


	public AIDALocation[] findVillages(String what,
								int max_result_len)
		 throws SQLException {
		if (what == null) return null;
		Connection con = getConnection();
		ArrayList items = new ArrayList();
		AIDALocation[] result = null;

		try {
			PreparedStatement ps = con.prepareStatement(FIND_VILLAGES);
			ps.setString(1, what + "%");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				AIDALocation item = new AIDALocation();

				Country country = new Country();
				Region region = new Region();
				County county = new County();
				Town town = new Town();
				Village village = new Village();

//                        "SELECT v.siruta_id,v.sirsup_id,v.name, " +
//                        "t.type,t.judet_id,t.name," +
//                        "cn.id, cn.regiune_id, cn.name, " +
//                        "r.country_iso3, r.name, " +
//                        "comparator.name " +
				village.setSiruta_id(rs.getInt(1));
				village.setSirsup_id(rs.getInt(2));
				village.setName(rs.getString(3));
				town.setType(rs.getByte(4));
				town.setJudet_id(rs.getInt(5));
				town.setName(rs.getString(6));
				town.setSirsup_id(village.getSirsup_id());
				county.setId(rs.getInt(7));
				county.setRegiune_id(rs.getInt(8));
				county.setName(rs.getString(9));
				region.setId(county.getRegiune_id());
				region.setCountryIso3(rs.getString(10));
				region.setName(rs.getString(11));
				country.setName(rs.getString(12));
				country.setIso3(region.getCountryIso3());

				item.setCountry(country);
				item.setRegion(region);
				item.setJudet(county);
				item.setOras(town);
				item.setLocalitate(village);

				items.add(item);

				if (items.size() > max_result_len) {
					break;
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlex) {
			throw sqlex;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new AIDALocation[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public long getLastId()
		 throws SQLException {
		Connection con = getConnection();
		long result = 0;

		try {
			PreparedStatement ps = con.prepareStatement(GET_LAST_ID);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	private static final String FIND_VILLAGES =
		 "SELECT v.siruta_id,v.sirsup_id,v.name, " +
		 "t.type,t.judet_id,t.name," +
		 "cn.id, cn.regiune_id, cn.name, " +
		 "r.country_iso3, r.name, " +
		 "c.name " +
		 "FROM towns t, regions r, counties cn, countries_iso3 c, villages v " +
		 "WHERE (c.iso3=r.country_iso3) " +
		 "AND (r.id=cn.regiune_id) " +
		 "AND (cn.id=t.judet_id) " +
		 "AND (v.sirsup_id=t.sirsup_id) " +
		 "AND (v.name ilike ?)";
	public static final String GET_ALL_FOR_ORAS =
		 "SELECT siruta_id, name " +
		 "FROM villages " +
		 "WHERE sirsup_id=? ORDER BY name ASC";
	public static final String GET_BY_ID =
		 "SELECT name, sirsup_id, gs_lat, gs_long, geo_code " +
		 "FROM villages " +
		 "WHERE siruta_id=?";
	public static final String INSERT_VILLAGE =
		 "insert " +
		 "into villages (siruta_id,sirsup_id,name,gs_lat,gs_long,geo_code) " +
		 "values (?,?,?,?,?,?)";
	public static final String UPDATE_VILLAGE =
		 "UPDATE villages " +
		 "SET name=?,gs_lat=?,gs_long=?,geo_code=? " +
		 "WHERE siruta_id=?";
	public static final String DELETE_VILLAGE =
		 "delete " +
		 "from villages " +
		 "where siruta_id=?";
	public static final String GET_LAST_ID =
		 "SELECT max(siruta_id) FROM villages";
}
